package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import co.gov.medellin.ssti.sub.model.dao.IUsuarioSubprogramaDAO;
import co.gov.medellin.ssti.sub.model.dao.exception.DAOException;
import co.gov.medellin.ssti.sub.model.vo.AreaVO;
import co.gov.medellin.ssti.sub.model.vo.UsuarioSubprogramaVO;
import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.UserVO;

public class UsuarioSubprogramaDAOImpl extends BaseDAOImpl implements
		IUsuarioSubprogramaDAO {

	public UsuarioSubprogramaDAOImpl(Connection con) {
		super(con);
	}

	public Collection<UsuarioSubprogramaVO> findUsuarios(String condicion)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT usu.CODIGO codeusu,usu.NOMBRE nomusu,usu.DESCRIPCION desusu,gusu.DESCRIPCION nomgrupo, gusu.CODIGO codegrupo "
				+ " FROM paoyer.TCS_USUARIO usu, paoyer.TCS_GRUPO_USUARIO gusu "
				+ " WHERE usu.GUS_CODIGO=gusu.CODIGO "
				+ " AND usu.campo_a1 IS NULL and gusu.campo_a1 IS NULL";
		boolean tieneCondicion = false;
		if (condicion != null && condicion.length() > 0
				&& !condicion.equals("null")) {
			tieneCondicion = true;
			query += " AND (usu.NOMBRE LIKE upper(?) OR usu.DESCRIPCION LIKE UPPER(?))";
		}
		ArrayList<UsuarioSubprogramaVO> lUsuarios = new ArrayList<UsuarioSubprogramaVO>();
		try {
			pstmt = conn.prepareStatement(query);
			if (tieneCondicion) {
				pstmt.setString(1, "%" + condicion + "%");
				pstmt.setString(2, "%" + condicion + "%");
			}
			rst = pstmt.executeQuery();

			while (rst.next()) {
				UsuarioSubprogramaVO usuarioVO = new UsuarioSubprogramaVO();
				usuarioVO.setCodeUsuario(new Long(rst.getLong("codeusu")));
				usuarioVO.setNombreUsuario(rst.getString("nomusu"));
				usuarioVO.setDescripcion(rst.getString("desusu"));
				usuarioVO.setCodeGrupo(new Long(rst.getString("codegrupo")));
				usuarioVO.setNombreGrupo(rst.getString("nomgrupo"));

				lUsuarios.add(usuarioVO);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return lUsuarios;
	}

	public long findSubprogramaSede(Long codeSubprograma, Long codeSede)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		int retornar = 0;
		String query = "SELECT CODIGO FROM TCS_BEN_SUBPROGRAMA_SEDE "
				+ "WHERE SEDE_CODIGO = ? AND SBPR_CODIGO = ? ";
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setLong(1, codeSede.longValue());
			pstmt.setLong(2, codeSubprograma.longValue());
			rst = pstmt.executeQuery();
			if (rst.next()) {
				retornar = rst.getInt("CODIGO");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return retornar;
	}

	public Collection<UsuarioSubprogramaVO> findNombres(Long codeSubprograma,
			Long codeSede, Long codePrograma, Long codeUsuario)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT prog.NOMBRE nomprog, subpro.NOMBRE nomsub, sede.NOMBRE nomsede, usu.NOMBRE nomusu, gus.descripcion descripcion_grupo "
				+ " FROM TCS_BEN_PROGRAMA prog, TCS_BEN_SUBPROGRAMA subpro, TCS_BEN_SEDE sede, paoyer.TCS_USUARIO usu, paoyer.TCS_GRUPO_USUARIO gus "
				+ " WHERE prog.CODIGO = ? AND subpro.CODIGO = ? "
				+ " AND sede.CODIGO = ? AND usu.CODIGO = ? "
				+ " AND gus.codigo = usu.gus_codigo ";
		Collection<UsuarioSubprogramaVO> results = new ArrayList<UsuarioSubprogramaVO>();
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setLong(1, codePrograma.longValue());
			pstmt.setLong(2, codeSubprograma.longValue());
			pstmt.setLong(3, codeSede.longValue());
			pstmt.setLong(4, codeUsuario.longValue());
			rst = pstmt.executeQuery();

			while (rst.next()) {
				UsuarioSubprogramaVO usuarioSubprogramaVO = new UsuarioSubprogramaVO();
				usuarioSubprogramaVO.setNombreprog(rst.getString("nomprog"));
				usuarioSubprogramaVO.setNombresubpro(rst.getString("nomsub"));
				usuarioSubprogramaVO.setNombresede(rst.getString("nomsede"));
				usuarioSubprogramaVO.setNombreUsuario(rst.getString("nomusu"));
				usuarioSubprogramaVO.setNombreGrupo(rst
						.getString("descripcion_grupo"));
				results.add(usuarioSubprogramaVO);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return results;
	}

	public boolean isUsuarioValido(Long codeUsuario, Long codeSubprogramaSede)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT CODIGO FROM TCS_BEN_USUARIO_SEDE "
				+ " WHERE USU_CODIGO = ? AND SPSE_CODIGO = ? ";
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setLong(1, codeUsuario.longValue());
			pstmt.setLong(2, codeSubprogramaSede.longValue());
			rst = pstmt.executeQuery();
			return rst.next();
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
	}

	public int insertUsuarioSubprograma(Long codeSubprogramaSede,
			Long codeUsuario) throws DAOException {
		PreparedStatement pstmt = null;

		String query = "INSERT INTO TCS_BEN_USUARIO_SEDE(CODIGO,USU_CODIGO, SPSE_CODIGO) "
				+ " VALUES(SCS_BEN_USE.NEXTVAL, ?, ?) ";
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setLong(1, codeUsuario.longValue());
			pstmt.setLong(2, codeSubprogramaSede.longValue());
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(pstmt);
			;
		}
	}

	public Collection<AreaVO> findSecretaria() throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String strSql = "SELECT codigo, nombre "
				+ " FROM tcs_ben_area WHERE area_codigo IS NULL ";

		ArrayList<AreaVO> secretarias = new ArrayList<AreaVO>();
		try {
			pstmt = conn.prepareStatement(strSql);
			rst = pstmt.executeQuery();
			while (rst.next()) {
				AreaVO areaVO = new AreaVO();
				areaVO.setCode(new Long(rst.getLong("codigo")));
				areaVO.setNombre(rst.getString("nombre"));
				secretarias.add(areaVO);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return secretarias;
	}

	public int insertPermisosReportes(String[] arr) throws DAOException {
		PreparedStatement pstmt = null;

		String query = "INSERT INTO TCS_BEN_SUBPROGRAMA_USUARIO(CODIGO, SBPR_CODIGO, "
				+ " USU_CODIGO, USUARIO_CREA, FECHA_CREA, TERMINAL_CREA, "
				+ " USUARIO_MODIFICA, FECHA_MODIFICA, TERMINAL_MODIFICA) "
				+ " VALUES(SCS_BEN_SUUS.NEXTVAL, ?, ?, ?, TO_DATE(?,'dd/mm/rrrr'), ?, ?, TO_DATE(?,'dd/mm/rrrr'), ?) ";
		long codeSubprograma = new Long(arr[0]).longValue();
		long codeUsuario = new Long(arr[1]).longValue();
		String usuarioCrea = arr[2];
		String fechaCrea = arr[3];
		String terminalCrea = arr[4];
		String usuarioModifica = arr[5];
		String fechaModifica = arr[6];
		String terminalModifica = arr[7];

		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setLong(1, codeSubprograma);
			pstmt.setLong(2, codeUsuario);
			pstmt.setString(3, usuarioCrea);
			pstmt.setString(4, fechaCrea);
			pstmt.setString(5, terminalCrea);
			pstmt.setString(6, usuarioModifica);
			pstmt.setString(7, fechaModifica);
			pstmt.setString(8, terminalModifica);

			return pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(pstmt);
		}
	}

	public List<UsuarioSubprogramaVO> findPermisosUsuarios(Long codeUsuario,
			Long codeSubprograma) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String strSql = "SELECT DISTINCT usu.codigo codusu,usu.NOMBRE nomuser, "
				+ " usu.DESCRIPCION descuser, subpro.NOMBRE nomsub, subpro.codigo "
				+ " FROM TCS_BEN_SUBPROGRAMA subpro, paoyer.tcs_usuario usu,TCS_BEN_SUBPROGRAMA_USUARIO subus "
				+ " WHERE subpro.codigo = subus.sbpr_codigo "
				+ " AND usu.CODIGO = subus.USU_CODIGO "
				+ " AND usu.codigo = ? AND subpro.codigo = ? ";

		List<UsuarioSubprogramaVO> results = new ArrayList<UsuarioSubprogramaVO>();

		try {
			pstmt = conn.prepareStatement(strSql);
			pstmt.setLong(1, codeUsuario.longValue());
			pstmt.setLong(2, codeSubprograma.longValue());
			rst = pstmt.executeQuery();
			if (rst.next()) {
				UsuarioSubprogramaVO objususubVO = new UsuarioSubprogramaVO();
				objususubVO.setNombreUsuario(rst.getString("nomuser"));
				objususubVO.setDescripcion(rst.getString("descuser"));
				objususubVO.setNombresubpro(rst.getString("nomsub"));
				results.add(objususubVO);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return results;
	}

	public List<UserVO> findGrupoUsuarios(Long codeUsuario) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT GUS_CODIGO,NOMBRE,DESCRIPCION FROM paoyer.TCS_USUARIO "
				+ " WHERE CODIGO = ? ";
		List<UserVO> results = new ArrayList<UserVO>();
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setLong(1, codeUsuario.longValue());
			rst = pstmt.executeQuery();
			if (rst.next()) {
				UserVO objUser = new UserVO();
				objUser.setDescription(rst.getString("DESCRIPCION"));
				objUser.setName(rst.getString("NOMBRE"));
				objUser.setUserGroupCode(new Long(rst.getString("GUS_CODIGO")));
				results.add(objUser);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return results;
	}

	public String findCodeSubprograma(Long codeSubprogramaSede)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT SBPR_CODIGO "
				+ "FROM TCS_BEN_SUBPROGRAMA_SEDE WHERE CODIGO = ? ";

		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setLong(1, codeSubprogramaSede.longValue());
			rst = pstmt.executeQuery();
			if (rst.next()) {
				return rst.getString("SBPR_CODIGO");
			} else {
				throw new SQLException("No existe el subprograma.");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
	}

	public Long findCodeSede(Long codeSubprogramaSede) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT SEDE_CODIGO "
				+ "FROM TCS_BEN_SUBPROGRAMA_SEDE WHERE CODIGO = ? ";

		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setLong(1, codeSubprogramaSede.longValue());
			rst = pstmt.executeQuery();
			if (rst.next()) {
				return new Long(rst.getLong("SEDE_CODIGO"));
			} else {
				throw new DAOException("No existe la sede.");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
	}

	public Long findCodePrograma(Long codeSubSede) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT sub.PRG_CODIGO codeProg "
				+ "FROM TCS_BEN_SUBPROGRAMA sub, TCS_BEN_SUBPROGRAMA_SEDE subSede "
				+ "WHERE subSede.CODIGO = ? "
				+ "AND subSede.SBPR_CODIGO = sub.CODIGO ";

		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setLong(1, codeSubSede.longValue());
			rst = pstmt.executeQuery();
			if (rst.next()) {
				return new Long(rst.getLong("codeProg"));
			} else {
				throw new SQLException("No existe el programa.");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
	}

}
